
import javax.swing.*;
import javax.swing.event.ListSelectionEvent;
import javax.swing.event.ListSelectionListener;
import javax.swing.table.DefaultTableColumnModel;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableColumn;
import javax.swing.table.TableModel;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.sql.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
* This code was edited or generated using CloudGarden's Jigloo
* SWT/Swing GUI Builder, which is free for non-commercial
* use. If Jigloo is being used commercially (ie, by a corporation,
* company or business for any purpose whatever) then you
* should purchase a license for each developer using Jigloo.
* Please visit www.cloudgarden.com for details.
* Use of Jigloo implies acceptance of these licensing terms.
* A COMMERCIAL LICENSE HAS NOT BEEN PURCHASED FOR
* THIS MACHINE, SO JIGLOO OR THIS CODE CANNOT BE USED
* LEGALLY FOR ANY CORPORATE OR COMMERCIAL PURPOSE.
*/
public class agent extends javax.swing.JFrame {
	private JLabel lblLogo;
	private JPanel block1;
	private JPanel divider2;
	private JButton btnIncnt;
	private JButton btnPkg;
	private JButton btnHome;
	private JPanel divider1;
	private JTable JTbAgent;
	private JButton btnLogout;
	private JScrollPane jScrollPane1;
	private JTextField txtBusPh;
	private JTextField txtLName;
	private JTextField txtInitial;
	private JButton btnAdd;
	private JComboBox cmbAgentActive;
	private JButton btnAssignAgent;
	private JList lstCustActive;
	private JLabel lbActiveList;
	private JLabel lbInactiveList;
	private JLabel lbAgent;
	private JButton btnBack;
	private JButton btnAssign;
	private JPanel jPanel1;
	private JScrollPane jScrollPane3;
	private JScrollPane jScrollPane2;
	private JList lstCustomeInactive;
	private JDialog dlgCustomersInactive;
	private JDialog DlgCustomers;
	private JTextField txtPosition;
	private JLabel lblPosition;
	private JButton btnReset;
	private JLabel lblAgt;
	private JButton btnDelete;
	private JButton btnUpdate;
	private JButton btnNew;
	private JComboBox JCmbAgyId;
	private JTextField txtEmail;
	private JTextField txtFName;
	private JTextField txtAgtId;
	private JLabel lblAgyId;
	private JLabel lblEmail;
	private JLabel lblBusPh;
	private JLabel lblLName;
	private JLabel lblInitial;
	private JLabel lblFName;
	private JLabel lblAgtId;
	private JLabel imgHome;
	
	//code by Feng Wang
	//declare table data
		private Vector<String> colNames = new Vector<String>();

		@SuppressWarnings("rawtypes")
		private Vector<Vector> dataRows = new Vector<Vector>();
		
		private Vector<String> CustInactive = new Vector<String>();
		
		private Vector<String> CustActive = new Vector<String>();
		
		private Vector<String> ActiveAgents = new Vector<String>();
		
		//code by Feng Wang
		//get database connect
				private  Statement getConnection() throws ClassNotFoundException, SQLException
				{
					Class.forName("com.mysql.jdbc.Driver");
					Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/travelexperts", "root","root");
					return conn.createStatement();
				}
				
				//code by Feng Wang
				//get agebt table col name
				public void loadColData() throws SQLException, ClassNotFoundException
				{

					Statement stmt = getConnection();
					ResultSet rs = stmt.executeQuery("Select A.AgentId, A.AgtFirstName, A.AgtMiddleInitial, A.AgtLastName, A.AgtBusPhone, A.AgtEmail, A.AgtPosition, concat(B.AgencyId,'-',B.AgncyAddress,'-',B.AgncyCity) Agency from Agents A, Agencies B where A.AgencyId = B.AgencyId");
					ResultSetMetaData rsmd = rs.getMetaData();
				
					for (int i = 1; i <= rsmd.getColumnCount(); i++)
					{
						colNames.add(rsmd.getColumnName(i));
					}
					stmt.close();
				}

				//code by Feng Wang
				//get agent data
				public void loadRowData() 
				{
					try {
						ResultSet rs;
						Statement stmt = getConnection();
						rs = stmt.executeQuery("Select A.AgentId, A.AgtFirstName, A.AgtMiddleInitial, A.AgtLastName, A.AgtBusPhone, A.AgtEmail, A.AgtPosition, concat(B.AgencyId,'-',B.AgncyAddress,'-',B.AgncyCity) Agency from Agents A, Agencies B where A.AgencyId = B.AgencyId");
						ResultSetMetaData rsmd = rs.getMetaData();
						
						while (rs.next())
						{
							Vector<String> row = new Vector<String>();
							for (int i = 1; i <= rsmd.getColumnCount(); i++)
							{
								row.add(rs.getString(i));
							}
							dataRows.add(row);
						}
						stmt.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					} catch (ClassNotFoundException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					
				}
				

				//code by Feng Wang
				//get agencies info
				public Vector<String> getAgencyIds() throws ClassNotFoundException, SQLException {
					// TODO Auto-generated method stub
					Vector<String> AgencyIds = new Vector<String>();

					Statement stmt = getConnection();
					ResultSet rs = stmt.executeQuery("Select * from Agencies");
					ResultSetMetaData rsmd = rs.getMetaData();
					while (rs.next())
					{
						AgencyIds.add(rs.getString("AgencyId") + "-" + rs.getString("AgncyAddress") + "-" + rs.getString("AgncyCity"));
					}
					stmt.close();
					return AgencyIds;
				}
				
				
				public void getActiveAgents() throws ClassNotFoundException, SQLException {
					// TODO Auto-generated method stub
					

					Statement stmt = getConnection();
					ResultSet rs = stmt.executeQuery("select * from Agents where AgtPosition <> 'Inactive'");
					ResultSetMetaData rsmd = rs.getMetaData();
					while (rs.next())
					{
						ActiveAgents.add(rs.getString("AgentId") + "-" + rs.getString("AgtFirstName") + " " + rs.getString("AgtLastName"));
					}
					stmt.close();

				}
				
				
				public void getCustInactive() throws ClassNotFoundException, SQLException {
					// TODO Auto-generated method stub

					CustInactive.clear();
					Statement stmt = getConnection();
					ResultSet rs = stmt.executeQuery("select C.CustomerId, C.CustFirstName, C.CustLastName from Customers C, Agents A where C.AgentId = A.AgentId and A.AgtPosition = 'Inactive'");
					ResultSetMetaData rsmd = rs.getMetaData();
					while (rs.next())
					{
						CustInactive.add(rs.getString("CustomerId") + "-" + rs.getString("CustFirstName") + " " + rs.getString("CustLastName"));
					}
					stmt.close();
				}
				
				
				//code by Feng Wang
				//update agent
				public boolean AgentUpdate(String AgentId, String AgtFirstName, String AgtMiddleInitial, String AgtLastName, 
						String AgtBusPhone, String AgtEmail, String AgtPosition, String AgencyId) throws ClassNotFoundException, SQLException
				{
					Statement stmt = getConnection();
					String sql = "Update Agents set AgtFirstName = '" + AgtFirstName + "', AgtMiddleInitial = '" + 
							AgtMiddleInitial + "', AgtLastName = '" + AgtLastName + "', AgtBusPhone = '" +
							AgtBusPhone + "', AgtEmail = '" + AgtEmail + "', AgtPosition = '" +
							AgtPosition + "', AgencyId = " + AgencyId + " where AgentId = " +
							AgentId;
					int numRows = stmt.executeUpdate(sql);
					stmt.close();
			        if (numRows == 0)
			        {
			        	return false;
			        }
			        else
			        {
			        	return true;
			        }
				}
				
				
				public boolean AgentAdd(String AgtFirstName, String AgtMiddleInitial, String AgtLastName, 
						String AgtBusPhone, String AgtEmail, String AgtPosition, String AgencyId) throws ClassNotFoundException, SQLException
				{
					Statement stmt = getConnection();
					String sql = "insert into  Agents (AgtFirstName,AgtMiddleInitial," + 
							"AgtLastName,AgtBusPhone, AgtEmail,AgtPosition,AgencyId) value('" + AgtFirstName + "','" + 
							AgtMiddleInitial + "', '" + AgtLastName + "', '" +
							AgtBusPhone + "','" + AgtEmail + "', '" +
							AgtPosition + "', " + AgencyId + ")";
					int numRows = stmt.executeUpdate(sql);
					stmt.close();
			        if (numRows == 0)
			        {
			        	return false;
			        }
			        else
			        {
			        	return true;
			        }
				}
				
				//code by Feng Wang
				//check agent has customers
				public boolean CheckCustomers(String AgentId) throws SQLException, ClassNotFoundException
				{
					Statement stmt = getConnection();
					ResultSet rs = stmt.executeQuery("Select count(*) CstNum from  Customers WHERE AgentId = " + AgentId);
					ResultSetMetaData rsmd = rs.getMetaData();
					while (rs.next())
					{
						if(Integer.parseInt(rs.getString("CstNum").toString()) >= 1)
						{
							stmt.close();
							return true;
						}
					}
					stmt.close();
					return false;
				}
				
				
				//code by Feng Wang
				//delete agent
				public boolean AgentDelete(String AgentId) throws ClassNotFoundException, SQLException
				{
					Statement stmt = getConnection();
					int numRows = 0;
					try {
						String sql = "DELETE FROM Agents WHERE AgentId = " + AgentId;
						numRows = stmt.executeUpdate(sql);
						stmt.close();
						if (numRows == 0)
				        {
				        	return false;
				        }
				        else
				        {
				        	return true;
				        }
					} catch (Exception e) {
						// TODO Auto-generated catch block
						System.out.println("Delete table error!");
						return false;
					}
				}
				
				
				//code by Feng Wang
				//update agent position to inactive
				public boolean AgentUpdateInactive(String AgentId) throws ClassNotFoundException, SQLException
				{
					Statement stmt = getConnection();
					int numRows = 0;
					try {
						String sql = "update Agents set AgtPosition = 'Inactive' WHERE AgentId = " + AgentId;
						numRows = stmt.executeUpdate(sql);
						stmt.close();
						if (numRows == 0)
				        {
				        	return false;
				        }
				        else
				        {
				        	return true;
				        }
					} catch (Exception e) {
						// TODO Auto-generated catch block
						System.out.println("Delete table error!");
						return false;
					}
				}
				
				
				//code by Feng Wang
				//update customers' agentid
				public boolean CustUpdateAgentId(String CustomerId, String AgentId) throws ClassNotFoundException, SQLException
				{
					Statement stmt = getConnection();
					String sql = "Update Customers set AgentId = " + AgentId + " where CustomerId = " + CustomerId;
					int numRows = stmt.executeUpdate(sql);
					stmt.close();
			        if (numRows == 0)
			        {
			        	return false;
			        }
			        else
			        {
			        	return true;
			        }
				}
				
				
				//code by Feng Wang
				//clear loadtext
				private void cleartext()
				{
					txtAgtId.setText("");
					txtFName.setText("");
					txtInitial.setText("");
					txtLName.setText("");
					txtBusPh.setText("");
					txtEmail.setText("");
					txtPosition.setText("");
					JCmbAgyId.setSelectedIndex(0);
				}
				
				
				//code by Feng Wang
				//vaild email load
				private boolean checkEmail(String email)
				{
					Pattern pattern = Pattern.compile("^([a-z0-9A-Z]+[-|\\.]?)+[a-z0-9A-Z]@([a-z0-9A-Z]+(-[a-z0-9A-Z]+)?\\.)+[a-zA-Z]{2,}$");
			        Matcher matcher = pattern.matcher(email);
			        if (matcher.matches())
			        {
			            return true;
			        }
			        return false;
			    }

				
				//code by Feng Wang
				//vaild phone load
				private boolean checkPhone(String phone)
				{
					Pattern pattern = Pattern.compile("^\\(?(\\d{3})\\)?[- ]?(\\d{3})[- ]?(\\d{4})$");
			        Matcher matcher = pattern.matcher(phone);
			        if (matcher.matches())
			        {
			            return true;
			        }
			        return false;
			    }

				//code by Feng Wang
				//vaild load
				private boolean vaildtext()
				{
					if (txtFName.getText().equals(""))
					{
						JOptionPane.showMessageDialog(null, "Please enter first name!");
						return false;
					}
					if (txtLName.getText().equals(""))
					{
						JOptionPane.showMessageDialog(null, "Please enter last name!");
						return false;
					}
					if (txtPosition.getText().equals(""))
					{
						JOptionPane.showMessageDialog(null, "Please enter position!");
						return false;
					}
					if(!checkEmail(txtEmail.getText()))
					{
						JOptionPane.showMessageDialog(null, "Email address is incorrect!");
						return false;
					}
					if(!checkPhone(txtBusPh.getText()))
					{
						JOptionPane.showMessageDialog(null, "Phone number is incorrect!");
						return false;
					}
					return true;
				}
				
				//code by Feng Wang
				//control Jable col size
				private void ctrcol(JTable table, int index, int width, int addlength)
				{
					TableColumn tcAgentId = table.getTableHeader().getColumnModel().getColumn(index);
					tcAgentId.setMaxWidth(width+addlength);
					tcAgentId.setPreferredWidth(width);
					tcAgentId.setWidth(width);
					tcAgentId.setMinWidth(width);
					JTbAgent.getTableHeader().getColumnModel().getColumn(index).setMaxWidth(width+addlength);
					JTbAgent.getTableHeader().getColumnModel().getColumn(index).setMinWidth(width);
				}
				
	/**
	* Auto-generated main method to display this JFrame
	*/
	public static void main(String[] args) {
		SwingUtilities.invokeLater(new Runnable() {
			public void run() {
				agent inst = new agent();
				inst.setLocationRelativeTo(null);
				inst.setVisible(true);
			}
		});
	}
	
	
	public agent() {
		super();
		initGUI();
	}
	
	
	private void initGUI() {
		try {
			
			setDefaultCloseOperation(WindowConstants.DISPOSE_ON_CLOSE);
			getContentPane().setLayout(null);
			getContentPane().setBackground(new java.awt.Color(255,255,255));
			this.setTitle("Travel Experts Agents");
			this.setResizable(false);
			{
				lblAgtId = new JLabel();
				getContentPane().add(lblAgtId);
				lblAgtId.setText("Agent ID");
				lblAgtId.setBounds(26, 110, 63, 14);
				lblAgtId.setFont(new java.awt.Font("SansSerif",1,12));
				lblAgtId.setHorizontalAlignment(SwingConstants.RIGHT);
			}
			{
				txtAgtId = new JTextField();
				getContentPane().add(txtAgtId);
				txtAgtId.setBounds(97, 107, 190, 22);
				txtAgtId.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
				//Mike Disabled the agentID text field as the agent Id
				//is auto generated and we do not want users to edit them
				
				txtAgtId.disable();
			}
			{
				lblFName = new JLabel();
				getContentPane().add(lblFName);
				lblFName.setText("First Name");
				lblFName.setBounds(1, 138, 88, 14);
				lblFName.setFont(new java.awt.Font("SansSerif",1,12));
				lblFName.setHorizontalAlignment(SwingConstants.RIGHT);
			}
			{
				txtFName = new JTextField();
				getContentPane().add(txtFName);
				txtFName.setBounds(97, 135, 190, 22);
				txtFName.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
			}
			{
				lblInitial = new JLabel();
				getContentPane().add(lblInitial);
				lblInitial.setText("Initial");
				lblInitial.setBounds(26, 166, 63, 14);
				lblInitial.setFont(new java.awt.Font("SansSerif",1,12));
				lblInitial.setHorizontalAlignment(SwingConstants.RIGHT);
			}
			{
				txtInitial = new JTextField();
				getContentPane().add(txtInitial);
				txtInitial.setBounds(97, 162, 59, 22);
				txtInitial.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
			}
			{
				lblLName = new JLabel();
				getContentPane().add(lblLName);
				lblLName.setText("Last Name");
				lblLName.setBounds(3, 194, 86, 14);
				lblLName.setFont(new java.awt.Font("SansSerif",1,12));
				lblLName.setHorizontalAlignment(SwingConstants.RIGHT);
			}
			{
				txtLName = new JTextField();
				getContentPane().add(txtLName);
				txtLName.setBounds(97, 190, 190, 22);
				txtLName.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
			}
			{
				lblBusPh = new JLabel();
				getContentPane().add(lblBusPh);
				lblBusPh.setText("Bus. Ph");
				lblBusPh.setBounds(-1, 222, 90, 14);
				lblBusPh.setFont(new java.awt.Font("SansSerif",1,12));
				lblBusPh.setHorizontalAlignment(SwingConstants.RIGHT);
			}
			{
				txtBusPh = new JTextField();
				getContentPane().add(txtBusPh);
				txtBusPh.setBounds(97, 218, 190, 22);
				txtBusPh.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
			}
			{
				lblEmail = new JLabel();
				getContentPane().add(lblEmail);
				lblEmail.setText("Email");
				lblEmail.setBounds(33, 250, 56, 14);
				lblEmail.setFont(new java.awt.Font("SansSerif",1,12));
				lblEmail.setHorizontalAlignment(SwingConstants.RIGHT);
			}
			{
				txtEmail = new JTextField();
				getContentPane().add(txtEmail);
				getContentPane().add(getLblPosition());
				getContentPane().add(getTxtPosition());
				txtEmail.setBounds(97, 245, 190, 22);
				txtEmail.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
			}
			{
				lblAgyId = new JLabel();
				getContentPane().add(lblAgyId);
				lblAgyId.setText("Agency ID");
				lblAgyId.setBounds(19, 302, 70, 18);
				lblAgyId.setFont(new java.awt.Font("SansSerif",1,12));
				lblAgyId.setHorizontalAlignment(SwingConstants.RIGHT);
			}
			{
				ComboBoxModel JCmbAgyIdModel = 
						new DefaultComboBoxModel(getAgencyIds());
				JCmbAgyId = new JComboBox();
				getContentPane().add(JCmbAgyId);
				JCmbAgyId.setModel(JCmbAgyIdModel);

				JCmbAgyId.setBounds(85, 285, 219, 22);

				JCmbAgyId.setBounds(97, 301, 190, 22);

				JCmbAgyId.setBackground(new java.awt.Color(255,255,255));
				JCmbAgyId.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
				JCmbAgyId.setFont(new java.awt.Font("SansSerif",0,12));
			}
			{
				btnDelete = new JButton();
				getContentPane().add(btnDelete);
				btnDelete.setText("Delete");
				btnDelete.setBounds(308, 286, 105, 22);
				btnDelete.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
				btnDelete.setFont(new java.awt.Font("SansSerif",1,12));
				btnDelete.setEnabled(false);
				btnDelete.setSize(105, 22);
				btnDelete.setPreferredSize(new java.awt.Dimension(14, 7));
				btnDelete.addActionListener(new ActionListener() {
					public void actionPerformed(ActionEvent evt) {
						//System.out.println("btnDelete.actionPerformed, event="+evt);
						//TODO add your code for btnDelete.actionPerformed
						//code by Feng Wang
						
						if (JTbAgent.getSelectedRow() == -1)
						{
							JOptionPane.showMessageDialog(null, "You must select a row first");
						}
						else
						{
							boolean ret;
							try {
								ret = CheckCustomers(txtAgtId.getText());
								if (ret)
								{
									int   result=JOptionPane.showConfirmDialog(null, "This agent has customers. you can not delete. Would you change position to inactive?",null,
											JOptionPane.YES_NO_OPTION);   
									if (result == JOptionPane.YES_OPTION)
									{
										if(AgentUpdateInactive(txtAgtId.getText()))
										{
											JOptionPane.showMessageDialog(null, "Position change to inactive successed");
											dataRows.clear();
											loadRowData();
											JTbAgent.repaint();
										}
										else
										{
											JOptionPane.showMessageDialog(null, "Updated agent failed!");
										}
									}
									else
									{
										dataRows.clear();
										loadRowData();
										JTbAgent.repaint();
										cleartext();
										btnNew.setEnabled(true);
										btnAdd.setEnabled(false);
										btnDelete.setEnabled(false);
										btnUpdate.setEnabled(false);
									}
								}
								else
								{
									int   result=JOptionPane.showConfirmDialog(null, "You want to delete this agent?",null,
											JOptionPane.YES_NO_OPTION);   
									if (result == JOptionPane.YES_OPTION)
									
									{
										try {
											ret = AgentDelete(txtAgtId.getText());
											if (ret)
											{
												dataRows.clear();
												loadRowData();
												JTbAgent.repaint();
												cleartext();
												JOptionPane.showMessageDialog(null, "deleted success");
												btnNew.setEnabled(true);
												btnAdd.setEnabled(false);
												btnDelete.setEnabled(false);
												btnUpdate.setEnabled(false);
											}
											else
											{
												JOptionPane.showMessageDialog(null, "deleted failed");
											}
										} catch (ClassNotFoundException e) {
											// TODO Auto-generated catch block
											e.printStackTrace();
										} catch (SQLException e) {
											// TODO Auto-generated catch block
											e.printStackTrace();
										}
									}
								}
							} catch (ClassNotFoundException | SQLException e1) {
								// TODO Auto-generated catch block
								e1.printStackTrace();
							}
						}
					}
					
				});
			}
			{
				btnAdd = new JButton();
				getContentPane().add(btnAdd);
				btnAdd.setText("Add");
				btnAdd.setBounds(308, 220, 105, 22);
				btnAdd.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
				btnAdd.setFont(new java.awt.Font("SansSerif",1,12));
				btnAdd.setEnabled(false);
				btnAdd.setSize(105, 22);
				btnAdd.setPreferredSize(new java.awt.Dimension(14, 7));
				btnAdd.addActionListener(new ActionListener() {
					public void actionPerformed(ActionEvent evt) {
						//System.out.println("btnAdd.actionPerformed, event="+evt);
						//TODO add your code for btnAdd.actionPerformed
						//code by Feng Wang
						
						if (vaildtext())
						{
							boolean ret = false;
						 
							try {
								String [] agentItem = JCmbAgyId.getSelectedItem().toString().split("-"); 
								ret = AgentAdd(txtFName.getText(), 
									txtInitial.getText(), txtLName.getText(), txtBusPh.getText(), 
									txtEmail.getText(), txtPosition.getText(), agentItem[0]);
								if (ret)
								{
									JOptionPane.showMessageDialog(null, "One Agent Added successfully");
									dataRows.clear();
									loadRowData();
									JTbAgent.repaint();
									cleartext();
									btnAdd.setEnabled(false);
									btnDelete.setEnabled(false);
									btnUpdate.setEnabled(false);
								}
								else
								{
									JOptionPane.showMessageDialog(null, "Addition Failed");
								}
								} catch (ClassNotFoundException e) {
									// TODO Auto-generated catch block
									e.printStackTrace();
								} catch (SQLException e) {
									// TODO Auto-generated catch block
									e.printStackTrace();
							}
						}
					}
				});
			}
			{
				btnUpdate = new JButton();
				getContentPane().add(btnUpdate);
				btnUpdate.setText("Update");
				btnUpdate.setBounds(308, 187, 105, 22);
				btnUpdate.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
				btnUpdate.setFont(new java.awt.Font("SansSerif",1,12));
				btnUpdate.setEnabled(false);
				btnUpdate.setSize(105, 22);
				btnUpdate.setPreferredSize(new java.awt.Dimension(14, 7));
				btnUpdate.addActionListener(new ActionListener() {
					public void actionPerformed(ActionEvent evt) {
						//System.out.println("btnUpdate.actionPerformed, event="+evt);
						//TODO add your code for btnUpdate.actionPerformed
						//code by Feng Wang
												
						if (vaildtext())
						{
							//update database
							boolean ret = false;
							try {
								String [] agentItem = JCmbAgyId.getSelectedItem().toString().split("-");
								ret = AgentUpdate(txtAgtId.getText(), txtFName.getText(), 
									txtInitial.getText(), txtLName.getText(), txtBusPh.getText(), 
									txtEmail.getText(), txtPosition.getText(), agentItem[0]);
								if (ret)
								{
									//change table data
				        	
									dataRows.clear();
				        	
									loadRowData();
							
									JTbAgent.repaint();
							
									JOptionPane.showMessageDialog(null, "updated successfully");
									btnUpdate.setEnabled(false);				        	
								}
								else
								{
									JOptionPane.showMessageDialog(null, "update failed");
								}
							} catch (ClassNotFoundException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							} catch (SQLException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							}
						}
					}
				});
			}
			{
				btnNew = new JButton();
				getContentPane().add(btnNew);
				btnNew.setText("New");
				btnNew.setBounds(308, 154, 105, 22);
				btnNew.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
				btnNew.setFont(new java.awt.Font("SansSerif",1,12));
				btnNew.setSize(105, 22);
				btnNew.setPreferredSize(new java.awt.Dimension(14, 7));
				btnNew.addActionListener(new ActionListener() {
					public void actionPerformed(ActionEvent evt) {
						//System.out.println("btnNew.actionPerformed, event="+evt);
						//TODO add your code for btnNew.actionPerformed
						//code by Feng Wang
						
						btnNew.setEnabled(false);
						btnAdd.setEnabled(true);
						btnDelete.setEnabled(false);
						btnUpdate.setEnabled(false);
						//text enable
						txtAgtId.setEditable(false);
						txtFName.setEditable(true);
						txtInitial.setEditable(true);
						txtLName.setEditable(true);
						txtBusPh.setEditable(true);
						txtEmail.setEditable(true);
						txtPosition.setEditable(true);
						JCmbAgyId.setEditable(true);
						cleartext();
					}
				});
			}
			{
				btnReset = new JButton();
				getContentPane().add(btnReset);
				getContentPane().add(getBtnAssignAgent());
				btnReset.setText("Reset");
				btnReset.setBounds(308, 253, 105, 22);
				btnReset.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
				btnReset.setFont(new java.awt.Font("SansSerif",1,12));
				btnReset.setSize(105, 22);
				btnReset.setPreferredSize(new java.awt.Dimension(14, 7));
				btnReset.addActionListener(new ActionListener() {
					public void actionPerformed(ActionEvent evt) {
						//System.out.println("btnReset.actionPerformed, event="+evt);
						//TODO add your code for btnReset.actionPerformed
						//code by Feng Wang
						
						btnNew.setEnabled(true);
						btnAdd.setEnabled(false);
						btnUpdate.setEnabled(false);
						btnDelete.setEnabled(false);
						cleartext();
					}
				});
			}
			{
				lblLogo = new JLabel();
				getContentPane().add(lblLogo);
				lblLogo.setBounds(24, 12, 257, 78);
				lblLogo.setBackground(new java.awt.Color(255,0,0));
				lblLogo.setIcon(new ImageIcon(getClass().getClassLoader().getResource("images/TravelLogo.jpg")));
			}
			{
				divider1 = new JPanel();
				getContentPane().add(divider1);
				divider1.setBounds(425, 26, 5, 300);
				divider1.setBackground(new java.awt.Color(0,0,0));
			}
			{
				divider2 = new JPanel();
				getContentPane().add(divider2);
				divider2.setBounds(690, 26, 5, 300);
				divider2.setBackground(new java.awt.Color(0,0,0));
			}
			{
				btnHome = new JButton();
				getContentPane().add(btnHome);
				btnHome.setIcon(new ImageIcon(getClass().getClassLoader().getResource("images/btnHm.jpg")));
				btnHome.setBounds(444, 26, 70, 300);
				btnHome.setFont(new java.awt.Font("SansSerif",1,12));
				btnHome.addMouseListener(new MouseAdapter() {
					public void mouseClicked(MouseEvent evt) {
						//System.out.println("btnHome.mouseClicked, event="+evt);
						//TODO add your code for btnHome.mouseClicked
						home inst = new home();
						inst.setLocationRelativeTo(null);
						inst.setVisible(true);
						agent.this.dispose();
					}
				});
			}
			{
				btnPkg = new JButton();
				getContentPane().add(btnPkg);
				btnPkg.setIcon(new ImageIcon(getClass().getClassLoader().getResource("images/btnPkg.jpg")));
				btnPkg.setBounds(526, 26, 70, 300);
				btnPkg.setFont(new java.awt.Font("SansSerif",1,12));
				btnPkg.addMouseListener(new MouseAdapter() {
					public void mouseClicked(MouseEvent evt) {
						//System.out.println("btnPkg.mouseClicked, event="+evt);
						//TODO add your code for btnPkg.mouseClicked
						pkg inst = new pkg();
						inst.setLocationRelativeTo(null);
						inst.setVisible(true);
						agent.this.dispose();
					}
				});
			}
			{
				btnIncnt = new JButton();
				getContentPane().add(btnIncnt);
				btnIncnt.setIcon(new ImageIcon(getClass().getClassLoader().getResource("images/BtnProd.jpg")));
				btnIncnt.setBounds(608, 26, 70, 300);
				btnIncnt.setFont(new java.awt.Font("SansSerif",1,12));
				btnIncnt.addMouseListener(new MouseAdapter() {
					public void mouseClicked(MouseEvent evt) {
						//System.out.println("btnIncnt.mouseClicked, event="+evt);
						//TODO add your code for btnIncnt.mouseClicked
						PkgProd inst = new PkgProd();
						inst.setLocationRelativeTo(null);
						inst.setVisible(true);
						agent.this.dispose();
					}
				});
			}
			{
				block1 = new JPanel();
				getContentPane().add(block1);
				block1.setBackground(new java.awt.Color(0,159,75));
				block1.setBounds(695, 101, 190, 225);
			}
			{
				loadColData();
				loadRowData();
				jScrollPane1 = new JScrollPane();
				getContentPane().add(jScrollPane1);
				jScrollPane1.setBounds(12, 338, 855, 140);
				jScrollPane1.setOpaque(false);
				{
					//code by Feng Wang
					
					TableModel JTbAgtModel = 
							new DefaultTableModel(dataRows,colNames);
					JTbAgent = new JTable()//table cant't Edit
					{
						public boolean isCellEditable(int row, int column)
						{
							return false;
						}
					};
					jScrollPane1.setViewportView(JTbAgent);
					jScrollPane1.setBackground(new java.awt.Color(255,255,255));
					jScrollPane1.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
					jScrollPane1.setWheelScrollingEnabled(false);
					JTbAgent.setModel(JTbAgtModel);
					
					//contrl column width
					ctrcol(JTbAgent, 0, 0, 0);
					ctrcol(JTbAgent, 1, 84, 50);
					ctrcol(JTbAgent, 2, 84, 50);
					ctrcol(JTbAgent, 3, 84, 50);
					ctrcol(JTbAgent, 4, 120, 50);
					ctrcol(JTbAgent, 5, 200, 50);
					ctrcol(JTbAgent, 6, 110, 50);

					JTbAgent.setBounds(6, 338, 754, 140);
					JTbAgent.setOpaque(false);
					JTbAgent.setFont(new java.awt.Font("SansSerif",0,10));
					JTbAgent.getSelectionModel().addListSelectionListener(new ListSelectionListener() {
						
						public void valueChanged(ListSelectionEvent e) {
							// TODO Auto-generated method stub
							
							//code by Feng Wang
							//put record to text
							txtAgtId.setText((String)JTbAgent.getValueAt(JTbAgent.getSelectedRow(), 0));
							txtFName.setText((String)JTbAgent.getValueAt(JTbAgent.getSelectedRow(), 1));
							txtInitial.setText((String)JTbAgent.getValueAt(JTbAgent.getSelectedRow(), 2));
							txtLName.setText((String)JTbAgent.getValueAt(JTbAgent.getSelectedRow(), 3));
							txtBusPh.setText((String)JTbAgent.getValueAt(JTbAgent.getSelectedRow(), 4));
							txtEmail.setText((String)JTbAgent.getValueAt(JTbAgent.getSelectedRow(), 5));
							txtPosition.setText((String)JTbAgent.getValueAt(JTbAgent.getSelectedRow(), 6));
							JCmbAgyId.setSelectedItem(JTbAgent.getValueAt(JTbAgent.getSelectedRow(), 7));
														
							btnNew.setEnabled(true);
							btnDelete.setEnabled(true);
							btnUpdate.setEnabled(true);
						}
					});
				}
			}
			{
				btnLogout = new JButton();
				getContentPane().add(btnLogout);
				btnLogout.setOpaque(false);
				btnLogout.setBorderPainted(false);
				btnLogout.setText("Logout");
				btnLogout.setBounds(799, 4, 69, 20);
				btnLogout.setFont(new java.awt.Font("SansSerif",0,10));
				btnLogout.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
				btnLogout.addMouseListener(new MouseAdapter() {
					public void mouseClicked(MouseEvent evt) {
						System.out.println("btnHome.mouseClicked, event="+evt);
						//TODO add your code for btnHome.mouseClicked
						home inst = new home();
						inst.setLocationRelativeTo(null);
						inst.setVisible(true);
						agent.this.dispose();
					}
				});
			}
			{
				imgHome = new JLabel();
				getContentPane().add(imgHome);
				imgHome.setBounds(0, 101, 425, 225);
				imgHome.setIcon(new ImageIcon(getClass().getClassLoader().getResource("images/agentbk2.jpg")));
				imgHome.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
			}
			{
				lblAgt = new JLabel();
				getContentPane().add(lblAgt);
				lblAgt.setText("AGENT");
				lblAgt.setBounds(707, 70, 160, 25);
				lblAgt.setFont(new java.awt.Font("SansSerif",1,24));
				lblAgt.setHorizontalAlignment(SwingConstants.CENTER);
			}
			pack();
			this.setSize(890, 519);
		} catch (Exception e) {
		    //add your error handling code here
			e.printStackTrace();
		}
		cleartext();
	}
	
	private JLabel getLblPosition() {
		if(lblPosition == null) {
			lblPosition = new JLabel();
			lblPosition.setText("Position");
			lblPosition.setBounds(12, 274, 76, 20);
			lblPosition.setFont(new java.awt.Font("SansSerif",1,12));
			lblPosition.setHorizontalAlignment(SwingConstants.RIGHT);
		}
		return lblPosition;
	}
	
	private JTextField getTxtPosition() {
		if(txtPosition == null) {
			txtPosition = new JTextField();
			txtPosition.setText("");
			txtPosition.setBounds(97, 274, 190, 22);
			txtPosition.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
		}
		return txtPosition;
	}
	
	private JDialog getDlgCustomersInactive() throws ClassNotFoundException, SQLException {
		if(dlgCustomersInactive == null) {
			dlgCustomersInactive = new JDialog(this);
			dlgCustomersInactive.getContentPane().setLayout(null);
			dlgCustomersInactive.setPreferredSize(new java.awt.Dimension(741, 472));
			dlgCustomersInactive.getContentPane().setLayout(null);
			dlgCustomersInactive.setDefaultLookAndFeelDecorated(true);
			dlgCustomersInactive.getContentPane().add(getJScrollPane2());
			dlgCustomersInactive.getContentPane().add(getJScrollPane3());
			dlgCustomersInactive.getContentPane().add(getJPanel1());
			dlgCustomersInactive.getContentPane().add(getCmbAgentActive());
			dlgCustomersInactive.getContentPane().add(getLbAgent());
			dlgCustomersInactive.getContentPane().add(getLbInactiveList());
			dlgCustomersInactive.getContentPane().add(getLbActiveList());


			dlgCustomersInactive.setSize(741, 472);
			dlgCustomersInactive.setLocationRelativeTo(null);
		}
		return dlgCustomersInactive;
	}
	
	private JList getLstCustomeInactive() throws ClassNotFoundException, SQLException {
		if(lstCustomeInactive == null) {
			getCustInactive();
			ListModel lstCustomeInactiveModel = 
					new DefaultComboBoxModel(CustInactive);
			
			lstCustomeInactive = new JList();
			lstCustomeInactive.setModel(lstCustomeInactiveModel);
			lstCustomeInactive.setBounds(113, 125, 229, 269);
			lstCustomeInactive.setPreferredSize(new java.awt.Dimension(240, 257));
		}
		return lstCustomeInactive;
	}
	
	private JScrollPane getJScrollPane2() throws ClassNotFoundException, SQLException {
		if(jScrollPane2 == null) {
			jScrollPane2 = new JScrollPane();
			jScrollPane2.setBounds(36, 78, 232, 260);
			jScrollPane2.setViewportView(getLstCustomeInactive());
		}
		return jScrollPane2;
	}
	
	private JList getLstCustActive() {
		if(lstCustActive == null) {
			ListModel lstCustomersActiveModel = 
					new DefaultComboBoxModel(CustActive);
			lstCustActive = new JList();
			lstCustActive.setModel(lstCustomersActiveModel);
			lstCustActive.setBounds(333, 226, 194, 257);
			lstCustActive.setPreferredSize(new java.awt.Dimension(224, 260));
		}
		return lstCustActive;
	}
	
	private JScrollPane getJScrollPane3() {
		if(jScrollPane3 == null) {
			jScrollPane3 = new JScrollPane();
			jScrollPane3.setBounds(409, 82, 218, 263);
			jScrollPane3.setViewportView(getLstCustActive());
		}
		return jScrollPane3;
	}
	
	private JPanel getJPanel1() {
		if(jPanel1 == null) {
			jPanel1 = new JPanel();
			jPanel1.setBounds(304, 82, 74, 211);
			jPanel1.setLayout(null);
			jPanel1.add(getBtnAssign());
			jPanel1.add(getBtnBack());
		}
		return jPanel1;
	}
	
	private JButton getBtnAssign() {
		if(btnAssign == null) {
			btnAssign = new JButton();
			btnAssign.setText(">>");
			btnAssign.setBounds(5, 67, 69, 23);
			btnAssign.addActionListener(new ActionListener() {
				public void actionPerformed(ActionEvent evt) {
					//System.out.println("btnAssign.actionPerformed, event="+evt);
					//TODO add your code for btnAssign.actionPerformed
					//code by Feng Wang
					
		
					if ((lstCustomeInactive.getSelectedIndex() < 0) || (lstCustomeInactive.getSelectedValue() == null))
					{
						JOptionPane.showMessageDialog(null, "You must choose a record");
					}
					else
					{
						String [] AgentInfo = cmbAgentActive.getSelectedItem().toString().split("-");
						String [] CustInfo = lstCustomeInactive.getSelectedValue().toString().split("-");
					
						try {
							if (CustUpdateAgentId(CustInfo[0], AgentInfo[0]))
							{

								CustActive.add(lstCustomeInactive.getSelectedValue().toString());
								
								ListModel lstCustomersActiveModel = 
										new DefaultComboBoxModel(CustActive);
								
								lstCustActive.setModel(lstCustomersActiveModel);
								lstCustActive.repaint();
								
								CustInactive.remove(lstCustomeInactive.getSelectedIndex());
								lstCustomeInactive.repaint();
							}
						} catch (ClassNotFoundException e) {
						// TODO Auto-generated catch block
							e.printStackTrace();
						} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
						}
					}
				}
			});
		}
		return btnAssign;
	}
	
	private JButton getBtnBack() {
		if(btnBack == null) {
			btnBack = new JButton();
			btnBack.setText("Back");
			btnBack.setBounds(5, 176, 69, 23);
			btnBack.addActionListener(new ActionListener() {
				public void actionPerformed(ActionEvent evt) {
					//System.out.println("btnBack.actionPerformed, event="+evt);
					//TODO add your code for btnBack.actionPerformed
					//code by Feng Wang
					try {
						getDlgCustomersInactive().setVisible(false);
					} catch (ClassNotFoundException | SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
			});
		}
		return btnBack;
	}
	
	private JButton getBtnAssignAgent() {
		if(btnAssignAgent == null) {
			btnAssignAgent = new JButton();
			btnAssignAgent.setText("Assign Cust");
			btnAssignAgent.setBounds(308, 121, 105, 23);
			btnAssignAgent.setFont(new java.awt.Font("SansSerif",1,12));
			btnAssignAgent.addActionListener(new ActionListener() {
				public void actionPerformed(ActionEvent evt) {
					//System.out.println("btnAssignAgent.actionPerformed, event="+evt);
					//TODO add your code for btnAssignAgent.actionPerformed
					//code by Feng Wang
					
					try {

						getDlgCustomersInactive().setVisible(true);
						CustInactive.clear();
						getCustInactive();
						ListModel lstCustomeInactiveModel = 
								new DefaultComboBoxModel(CustInactive);
						lstCustomeInactive.setModel(lstCustomeInactiveModel);
						lstCustomeInactive.repaint();
						
						CustActive.clear();
						ListModel lstCustomersActiveModel = 
								new DefaultComboBoxModel(CustActive);
						
						lstCustActive.setModel(lstCustomersActiveModel);
						lstCustActive.repaint();
						lstCustActive.repaint();
						
						ActiveAgents.clear();
						getActiveAgents();
						cmbAgentActive.repaint();
						cmbAgentActive.setSelectedIndex(0);
						
					} catch (ClassNotFoundException | SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
			});
		}
		return btnAssignAgent;
	}
	
	private JComboBox getCmbAgentActive() throws ClassNotFoundException, SQLException {
		if(cmbAgentActive == null) {
			getActiveAgents();
			ComboBoxModel cmbAgentActiveModel = 
					new DefaultComboBoxModel(ActiveAgents);
			cmbAgentActive = new JComboBox();
			cmbAgentActive.setModel(cmbAgentActiveModel);
			cmbAgentActive.setBounds(189, 29, 298, 23);
		}
		return cmbAgentActive;
	}
	
	private JLabel getLbAgent() {
		if(lbAgent == null) {
			lbAgent = new JLabel();
			lbAgent.setText("Active Agents");
			lbAgent.setBounds(68, 32, 92, 16);
		}
		return lbAgent;
	}
	
	private JLabel getLbInactiveList() {
		if(lbInactiveList == null) {
			lbInactiveList = new JLabel();
			lbInactiveList.setText("Inactive Customers");
			lbInactiveList.setBounds(59, 54, 130, 16);
		}
		return lbInactiveList;
	}
	
	private JLabel getLbActiveList() {
		if(lbActiveList == null) {
			lbActiveList = new JLabel();
			lbActiveList.setText("Active Customers");
			lbActiveList.setBounds(437, 61, 125, 16);
		}
		return lbActiveList;
	}
}
